16

I am just new to reading in an XML file into a table through SQL Server Management Studio. There are probably better ways but I would like to use this approach.

Currently I am reading in a standard XML file of records on people. A <record> tag is the highest level of each row of data. I want to read all the records into separate rows into my SQL table.

I have gotten along fine so far using the following approach as follows:

SELECT
        -- Record
        category, editor, entered, subcategory, uid, updated,
        -- Person
        first_name, last_name, ssn, ei, title, POSITION,
FROM OPENXML(@hDoc, 'records/record/person/names')
WITH 
(
    -- Record
    category [varchar](100) '../../@category',
    editor [varchar](100) '../../@editor',
    entered Datetime '../../@entered',
    subcategory [varchar](100) '../../@subcategory',
    uid BIGINT '../../@uid',
    updated [varchar](100) '../../@updated',
    -- Person
    first_name [varchar](100) 'first_name',
    last_name [varchar](100) 'last_name',
    ssn [varchar](100) '../@ssn',
    ei [varchar](100) '../@e-i',
    title [varchar](100) '../title',
    Position [varchar](100) '../position',  
)

However this approach has worked fine as the tag names have all been unique to each record/person. The issue I have is within the <Person> tag I now have an <Aliases> tag that contains a list of more than 1 <Alias> test name </Alias> tags. If I use the above approach & reference '../aliases' I get all the Alias elements as one long String row mixed together. If I just try '../aliases/alias' ONLY the first element is returned per record row. If there was 10 Alias elements within the Aliases tag set I would like 10 rows returned for example.

Is there a way to specify that when there are multiple tags of the same name within a higher level tag, return them all & not just one row?

The following is the example block within the XML I am referring to:

- <aliases>
  <alias>test 1</alias> 
  <alias>test 2</alias> 
  <alias>test 3</alias> 
  </aliases>

I would like the following in the SQL table:

Record               Aliases

Record 1             test 1
Record 1             test 2
Record 1             test 3
Record 2             test 4
Record 2             test 5

but all I get is:

Record 1             test 1 
Record 2             test 4

Apologies if I have not explained this correctly - any help would be greatly appreciated.

Malachi
  • 3,205
  • 4
  • 29
  • 46
user3441467
  • 161
  • 6
  • Did you read this? http://technet.microsoft.com/en-us/library/ms186918.aspx – PeterRing Mar 20 '14 at 11:00
  • Can you test it with OPENXML(@hDoc, 'records/record/person/names',2)? – PeterRing Mar 20 '14 at 11:03
  • Thank you for your reply. I have tried OPENXML(@hDoc, 'records/record/person/names',2) but all the alias elements are being added as one long string per record row. I have read the link you provided & can see why you suggested that approach which looks like it should have worked. – user3441467 Mar 20 '14 at 11:17
  • 2
    Found this link which solved the problem - http://stackoverflow.com/questions/8013036/parsing-openxml-with-multiple-elements-of-the-same-name – user3441467 Mar 20 '14 at 14:24
  • please post what you did as an answer so we can see what you did, and then accept it as an answer when you can. this helps us all learn. – Malachi Mar 29 '14 at 17:35
  • I used the following link http://stackoverflow.com/questions/8013036/parsing-openxml-with-multiple-elements-of-the-same-name which says that you can use a dot '.' to return all instances of multiple tags with the same name & it worked fine. – user3441467 Apr 02 '14 at 09:57

4 Answers4

1

As Antonio indicated, I would use XQuery instead of Openxml. I've guessed at your source xml, and provided code for your 2 queries:

/* Please note I have used different cases than you to follow a standard (first letter capitialised for elements, lowercase for attributes.
   As xml is case sensitive, you may need to change the below code to suit the case of your data */

/* Bring your xml into an xml variable */

DECLARE @xml xml = '
<Records>
    <Record category="category1" editor="editor1" entered="2015-01-01" subcategory="subcategory1" uid="100001" updated="updated1">
        <Person ssn="ssn1" e-i="ei1">
            <Title>Title1</Title>
            <Position>Position1</Position>
            <Names>
                <First_name>FirstName1</First_name>
                <Last_name>LastName1</Last_name>
                <Aliases>
                    <Alias>test1</Alias>
                    <Alias>test2</Alias>
                    <Alias>test3</Alias>
                </Aliases>
            </Names>
        </Person>
    </Record>
    <Record category="category2" editor="editor2" entered="2015-01-02" subcategory="subcategory2" uid="100002" updated="updated2">
        <Person ssn="ssn2" e-i="ei2">
            <Title>Title2</Title>
            <Position>Position2</Position>
            <Names>
                <First_name>FirstName2</First_name>
                <Last_name>LastName2</Last_name>
                <Aliases>
                    <Alias>test4</Alias>
                    <Alias>test5</Alias>
                </Aliases>
            </Names>
        </Person>
    </Record>
</Records>'


/* The unary relationship of 1 element/attribute type per record in xpath */

SELECT T.rows.value('@category[1]', '[varchar](100)') AS Category
      ,T.rows.value('@editor[1]', '[varchar](100)') AS Editor
      ,T.rows.value('@entered[1]', '[Datetime]') AS Entered
      ,T.rows.value('@subcategory[1]', '[varchar](100)') AS Subcategory
      ,T.rows.value('@uid[1]', '[bigint]') AS [UID]
      ,T.rows.value('@updated[1]', '[varchar](100)') AS Updated
      ,T.rows.value('(Person/Names/First_name)[1]', '[varchar](100)') AS First_Name
      ,T.rows.value('(Person/Names/Last_name)[1]', '[varchar](100)') AS Last_Name
      ,T.rows.value('(Person/@ssn)[1]', '[varchar](100)') AS SSN
      ,T.rows.value('(Person/@e-i)[1]', '[varchar](100)') AS ei
      ,T.rows.value('(Person/Title)[1]', '[varchar](100)') AS Title
      ,T.rows.value('(Person/Position)[1]', '[varchar](100)') AS Position
FROM @xml.nodes('/Records/Record') T(rows)


/* Record to alias, one-to-many mapping */

SELECT T.rows.value('../../../../@category[1]', '[varchar](100)') AS Category
      ,T.rows.value('.', '[varchar](100)') AS Alias
FROM @xml.nodes('/Records/Record/Person/Names/Aliases/Alias') T(rows)
0

Try modifying the FROM clause as follows:

SELECT
...

FROM OPENXML(@hDoc, 'records/record/person/aliases')
Paul Shiryaev
  • 161
  • 1
  • 9
0

When I work with XML files i do the following to split the nodes into columns:

XML file sitting in a column in a table with the following format:

<CustomData>
<Name>Shaun</Name>
<Surname>Johnson</Surname>
<Title>Mr</Title>
<Age>29</Age>
</CustomData>

The Select statement I use:

SELECT  
[ColumnName].value('(/CustomData//Name/node())[1]' , 'nvarchar(max)') AS Name,
[ColumnName].value('(/CustomData//Surname/node())[1]','nvarchar(max)') AS Surname,
[ColumnName].value('(/CustomData//Title/node())[1]' , 'nvarchar(max)') AS Title,
[ColumnName].value('(/CustomData//Age/node())[1]' , 'nvarchar(max)') AS Age
FROM TableName

Results:

Name  Surname  Title  Age
----  -------  -----  ---
Shaun Johnson  Mr     29
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
shaunfocus
  • 11
  • 2
-1

openxml must return a scalar value, you should use the .nodes method in XML joined with your OPENXML

jmunsch
  • 22,771
  • 11
  • 93
  • 114
Antonio
  • 535
  • 10
  • 26