Results 1 to 2 of 2

Thread: XM Query

  1. #1
    Join Date
    Sep 2014
    Posts
    1

    Unanswered: XM Query

    HI, i'm trying to parse some XML and I can't get the damn thing to return any values.

    I need to return the values for <linx> as separate rows , can somebody point me in the right direction as to what I need to do.

    Query =
    SELECT
    ORD.value('(linx/text())[1]','varchar(50)') as Linx

    FROM table CROSS APPLY
    OrderXml.nodes('/Lines/OrderLine') AS orders(ORD)





    XML =
    <Lines>
    <OrderLine>
    <linx>076924</linx>
    <Quantity>2</Quantity>
    <ItemPrice>150.46</ItemPrice>
    <PartNumber i:nil="true" />
    <CustomValues>
    <CustomField>
    <Name>RNID</Name>
    <Value>20192051</Value>
    </CustomField>
    </CustomValues>
    </OrderLine>
    <OrderLine>
    <linx>070843</linx>
    <Quantity>2</Quantity>
    <ItemPrice>21.15</ItemPrice>
    <PartNumber i:nil="true" />
    <CustomValues>
    <CustomField>
    <Name>RNID</Name>
    <Value>20192052</Value>
    </CustomField>
    </CustomValues>
    </OrderLine>
    <OrderLine>
    <linx>076931</linx>
    <Quantity>1</Quantity>
    <ItemPrice>11.23</ItemPrice>
    <PartNumber i:nil="true" />
    <CustomValues>
    <CustomField>
    <Name>RNID</Name>
    <Value>20192053</Value>
    </CustomField>
    </CustomValues>
    </OrderLine>
    </Lines>

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE @x xml = '
    <Lines>
      <OrderLine>
        <linx>076924</linx>
        <Quantity>2</Quantity>
        <ItemPrice>150.46</ItemPrice>
        <CustomValues>
          <CustomField>
            <Name>RNID</Name>
            <Value>20192051</Value>
          </CustomField>
        </CustomValues>
        </OrderLine>
      <OrderLine>
        <linx>070843</linx>
        <Quantity>2</Quantity>
        <ItemPrice>21.15</ItemPrice>
        <CustomValues>
          <CustomField>
            <Name>RNID</Name>
            <Value>20192052</Value>
          </CustomField>
        </CustomValues>
      </OrderLine>
      <OrderLine>
        <linx>076931</linx>
        <Quantity>1</Quantity>
        <ItemPrice>11.23</ItemPrice>
        <CustomValues>
          <CustomField>
            <Name>RNID</Name>
            <Value>20192053</Value>
          </CustomField>
        </CustomValues>
      </OrderLine>
    </Lines>
    ';
    
    DECLARE @t table (
       z xml
    );
    
    INSERT INTO @t (z)
      VALUES (@x)
    ;
    
    SELECT linx.value('.', 'varchar(50)') As linx
    FROM   @x.nodes('/Lines/OrderLine/linx') x(linx)
    ;
    
    SELECT z.OrderLine.value('linx[1]'     , 'varchar(50)') As linx
         , z.OrderLine.value('Quantity[1]' , 'int') As Quantity
         , z.OrderLine.value('ItemPrice[1]', 'money') As ItemPrice
         , z.OrderLine.value('(CustomValues/CustomField/Name)[1]' , 'varchar(50)') As Name
         , z.OrderLine.value('(CustomValues/CustomField/Value)[1]', 'char(8)') As Value
    FROM   @t As t
     CROSS
     APPLY z.nodes('Lines/OrderLine') z(OrderLine)
    ;
    Results:
    Code:
    linx
    --------------------------------------------------
    076924
    070843
    076931
    
    linx                                               Quantity    ItemPrice             Name                                               Value
    -------------------------------------------------- ----------- --------------------- -------------------------------------------------- --------
    076924                                             2           150.46                RNID                                               20192051
    070843                                             2           21.15                 RNID                                               20192052
    076931                                             1           11.23                 RNID                                               20192053
    Last edited by gvee; 04-30-15 at 05:30.
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •