Parsing XML in SQL Server 2005

Question

In SQL Server 2005, i am trying to parse below XML to details in following format, but i am unable to do it. Appreciate any help. I want my select output like

1 TagSold Tag        0   101 2
2 TagReg  Customer   101 0   4
3 TagAdj  Tag        0   105 2



<TagDistributor>
<stbLedgerEntryTypes>
    <stbLedgerEntryType type="TagSold">
        <vcMapping>Tag</vcMapping>
        <tiCustTrxnTypeID>0</tiCustTrxnTypeID>
        <tiTagTrxnTypeID>101</tiTagTrxnTypeID>
        <tiPaymentTypeID>2</tiPaymentTypeID>
    </stbLedgerEntryType>
    <stbLedgerEntryType type="TagReg">
        <vcMapping>Customer</vcMapping>
        <tiCustTrxnTypeID>101</tiCustTrxnTypeID>
        <tiTagTrxnTypeID>0</tiTagTrxnTypeID>
        <tiPaymentTypeID>4</tiPaymentTypeID>
    </stbLedgerEntryType>
    <stbLedgerEntryType type="TagAdj">
        <vcMapping>Tag</vcMapping>
        <tiCustTrxnTypeID>0</tiCustTrxnTypeID>
        <tiTagTrxnTypeID>105</tiTagTrxnTypeID>
        <tiPaymentTypeID>2</tiPaymentTypeID>
    </stbLedgerEntryType>
</stbLedgerEntryTypes></TagDistributor>

I tried the follwing. I got the output. but in the first column i don't get the attribute value to diffrentiate. I have doubt in first line.

SELECT 
         a.b.query('.').value('@type', 'varchar(128)'),
         a.b.query('vcMapping').value('.', 'varchar(128)'),
         a.b.query('tiCustTrxnTypeID').value('.', 'int'),
         a.b.query('tiTagTrxnTypeID').value('.', 'int'),
         a.b.query('tiPaymentTypeID').value('.', 'int')
FROM    @ipv_xmlDistributorInfo.nodes('TagDistributor/stbLedgerEntryTypes/stbLedgerEntryType') a(b)

sgeddes · Accepted Answer · 2013-02-03 06:40:22Z

1

Something like this should work:

SELECT 
   x.value('(@type)[1]', 'varchar(100)') AS 'Type',
   x.value('(vcMapping)[1]', 'varchar(100)') AS 'vcMapping',
   x.value('(tiCustTrxnTypeID)[1]', 'int') AS 'tiCustTrxnTypeID',
   x.value('(tiTagTrxnTypeID)[1]', 'int') AS 'tiTagTrxnTypeID',
   x.value('(tiPaymentTypeID)[1]', 'int') AS 'tiPaymentTypeID'
FROM XMLTable x
CROSS APPLY x.myXMLField.nodes('/TagDistributor/stbLedgerEntryTypes/stbLedgerEntryType') 
  n(x)

And here is the SQL Fiddle.

Good luck.

answered Feb 3, 2013 at 6:40

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sgeddes Over a year ago

If you need the ROW_NUMBER, then this should work: sqlfiddle.com/#!3/3557c/3

Collectives™ on Stack Overflow

Parsing XML in SQL Server 2005

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related